package sgc.beans;

import java.io.IOException;
import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.bean.SessionScoped;
import javax.faces.component.html.HtmlDataTable;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContext;
import javax.sql.DataSource;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.view.JasperViewer;
import sgc.struc.Req_Header;
import sgc.struc.Req_Position;

@ManagedBean(name = "purchaseReqListBean")
@RequestScoped
public class PurchaseReqListBean implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Req_Header header;
	private List<Req_Header> headers;
	private List<Req_Header> filter_headers;
	private List<Req_Position> positions;
	private DataSource ds;
	private int company_code;
	private String company_name;
	private HtmlDataTable htmlDataTable;

	public HtmlDataTable getHtmlDataTable() {
		return htmlDataTable;
	}

	public void setHtmlDataTable(HtmlDataTable htmlDataTable) {
		this.htmlDataTable = htmlDataTable;
	}

	public List<Req_Header> getFilter_headers() {
		return filter_headers;
	}

	public void setFilter_headers(List<Req_Header> filter_headers) {
		this.filter_headers = filter_headers;
	}

	public PurchaseReqListBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		header = new Req_Header();
		positions = new ArrayList<Req_Position>();

		FacesContext context = FacesContext.getCurrentInstance();
		CheckUserBean cu = (CheckUserBean) context.getApplication()
				.evaluateExpressionGet(context, "#{checkUserBean}",
						CheckUserBean.class);

		company_code = cu.getUsuario().getCompany_code();
		company_name = cu.getUsuario().getCompany();
	}

	public List<Req_Header> getHeaders() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getAllReqHeader(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<Req_Header> list = new ArrayList<Req_Header>();

		while (result.next()) {
			Req_Header aux_header = new Req_Header();

			aux_header.setReq_no(result.getInt(2));
			aux_header.setFec_sol(result.getDate(3));
			aux_header.setFec_req(result.getDate(4));
			aux_header.setSolicitante(result.getString(5));
			aux_header.setCecos(result.getString(7));
			aux_header.setSuplier(result.getString("sup_name4") + " " + result.getString("sup_name3") + ", "
					+ result.getString("sup_name1") + " " + result.getString("sup_name2"));
			aux_header.setReq_deta(result.getString(12));
			aux_header.setReq_status(result.getString(13));

			list.add(aux_header);
		}
		con.close();

		return list;
	}

	public void setHeaders(List<Req_Header> headers) {
		this.headers = headers;
	}

	private void getPositions() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getReqPosition(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.setInt(2, header.getReq_no());
		cs.execute();
		ResultSet result = cs.getResultSet();
		positions.clear();
		while (result.next()) {
			Req_Position aux_pos = new Req_Position();

			aux_pos.setReq_no(result.getInt(1));
			aux_pos.setReq_pos(result.getInt(2));
			aux_pos.setMat_name(result.getString(4));
			aux_pos.setMat_cant((int) result.getDouble(6));
			aux_pos.setMat_unit(result.getString(8));
			positions.add(aux_pos);
		}
	}

	public Req_Header getHeader() {
		return header;
	}

	public void setHeader(Req_Header header) {
		this.header = header;
	}

	private void printInternal() throws JRException {

		ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance()
				.getExternalContext().getContext();
		String sourceFileName = ctx.getRealPath("/reports/PurchaseReq.jasper");
		String outputFileName = ctx.getRealPath("/private/" + company_code)
				+ "/PREQ_" + company_code + "_" + header.getReq_no() + ".pdf";
		JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(
				positions);
		HashMap parameterMap = new HashMap();
		parameterMap.put("act_company", company_name);
		parameterMap.put("req_header", header);

		JasperPrint print = JasperFillManager.fillReport(sourceFileName,
				parameterMap, dataSource);
		JasperExportManager.exportReportToPdfFile(print,outputFileName);
		JasperViewer.viewReport(print, false);

	}

	public String print() throws SQLException, JRException {
			getPositions();
			printInternal();
			return "OK";
	}

	public void inicio() throws IOException {
		FacesContext.getCurrentInstance().getExternalContext()
				.redirect("navegacion.xhtml");
	}

	public void logout() throws IOException {
		FacesContext.getCurrentInstance().getExternalContext()
				.invalidateSession();
	}

}
